package com.hyt.it.ogt.ykcj.service.grade.impl;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSONArray;
import com.hyt.it.ogt.ykcj.domain.vo.export.ExportEnterpriseExamineeStemScoreVo;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.hyt.it.ogt.ykcj.common.core.domain.AjaxResult;
import com.hyt.it.ogt.ykcj.common.exception.CustomException;
import com.hyt.it.ogt.ykcj.common.utils.poi.ExcelUtil;
import com.hyt.it.ogt.ykcj.domain.vo.export.ExportEnterpriseExamineeScoreVo;
import com.hyt.it.ogt.ykcj.domain.vo.export.ExportEnterpriseExamineeSubjectGradeScoreVo;
import com.hyt.it.ogt.ykcj.service.system.IFileUpHwCloudService;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;

/**
 * 企业招聘考生信息导出excle工具类
 *
 * @author yaojian
 * @date 2021-11-24
 */
@Component
public class EnterpriseExamineeInfoExportUtil {

    private static final Logger log = LoggerFactory.getLogger(EnterpriseExamineeInfoExportUtil.class);
    
    @Autowired
    private IFileUpHwCloudService fileUpHwCloudService;
    

    /**
     * 导出企业招聘学生信息集合的数据
     *
     * @param collect   数据集合
     * @param clazz     导出集合中类的class
     * @param sheetName sheet名称
     * @return
     */
    public static <T> AjaxResult exportExcel(List<T> collect, Class<T> clazz, String sheetName) {
        Workbook wb = null;
        FileOutputStream out = null;
        String filename = "";
        try {
            filename = ExcelUtil.encodingFilename(sheetName);
            out = new FileOutputStream(ExcelUtil.getAbsoluteFile(filename));
            wb = ExcelExportUtil.exportExcel(new ExportParams(null, sheetName), clazz, collect);
            wb.write(out);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new CustomException("导出Excel失败，请联系管理员！");
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return AjaxResult.success(filename);
    }


    /**
     * 1.1：导出 XSSFWorkbook
     * 导出workbook学生 考试总成绩
     *
     * @param values 数据
     * @param titles                         excle的列表头
     * @param sheetName                           sheet名称
     * @return
     */
    public static XSSFWorkbook getExamineeXSSFWorkbook(List<String> titles, List<String> values, String sheetName) {

        //1.创建XSSFWorkbook，一个XSSFWorkbook对应的Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();

        //2.在work中添加一个sheet,对应excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        XSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);

        //单元格样式设置
        XSSFCellStyle alignStyle = wb.createCellStyle();
        alignStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        alignStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        alignStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        alignStyle.setBorderBottom(BorderStyle.THIN);
        alignStyle.setBorderLeft(BorderStyle.THIN);
        alignStyle.setBorderRight(BorderStyle.THIN);
        alignStyle.setBorderTop(BorderStyle.THIN);
        alignStyle.setFont(titleFont);
        alignStyle.setWrapText(true);

        //3.设置表头，即每个列的列名
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 700);
        //将列名写入
        List<ExcelHead> titleList = new LinkedList<>();
        for (String str : titles) {
            titleList.add(new ExcelHead(1, str));
        }
        //设置列名的名称和样式
        setCellStyle(  titleList,  row,  alignStyle,  wb, titles.size());

        //写入每行的正式数据
        for (int i = 0; i < values.size(); i++) {
            if(StringUtils.isEmpty(values.get(i))){
                continue;
            }
            //创建行
            row = sheet.createRow(i + 1);
            List<String> cellValues = JSONArray.parseArray(values.get(i), String.class);
            for (int j = 0; j < cellValues.size(); j++) {
                //在行里插入内容
                row.createCell(j).setCellValue(cellValues.get(j));
            }
        }
        /**
         * 设置单元格宽度
         * 上面的操作已经是生成一个完整的文件了，只需要将生成的流转换成文件即可；
         * 下面的设置宽度可有可无，对整体影响不大
         */
        for (int i = 0; i <= titleList.size(); i++) {
            sheet.setColumnWidth(i, 3300);
        }
        return wb;
    }


    /**
     * 1.1：导出 XSSFWorkbook
     * 导出workbook学生 考试总成绩
     *
     * @param exportEnterpriseExamineeScoreVoList 数据
     * @param subjectList                         excle的列表头
     * @param sheetName                           sheet名称
     * @return
     */
    public static XSSFWorkbook getExamineeSubjectXSSFWorkbook(List<ExportEnterpriseExamineeScoreVo> exportEnterpriseExamineeScoreVoList,
                                                              List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList, String sheetName) {

        //1.创建XSSFWorkbook，一个XSSFWorkbook对应的Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();

        //2.在work中添加一个sheet,对应excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        XSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);

        //单元格样式设置
        XSSFCellStyle alignStyle = wb.createCellStyle();
        alignStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        alignStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        alignStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        alignStyle.setBorderBottom(BorderStyle.THIN);
        alignStyle.setBorderLeft(BorderStyle.THIN);
        alignStyle.setBorderRight(BorderStyle.THIN);
        alignStyle.setBorderTop(BorderStyle.THIN);
        alignStyle.setFont(titleFont);
        alignStyle.setWrapText(true);

        //3.设置表头，即每个列的列名
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 700);
        //将列名写入
        List<ExcelHead> titleList = new LinkedList<>();
        titleList.add(new ExcelHead(1, "准考证号"));
        titleList.add(new ExcelHead(1, "姓名"));
        titleList.add(new ExcelHead(1, "性别"));
        titleList.add(new ExcelHead(1, "证件号"));
        titleList.add(new ExcelHead(1, "手机号码"));
        titleList.add(new ExcelHead(1, "学历"));
        titleList.add(new ExcelHead(1, "总分"));
        titleList.add(new ExcelHead(1, "考试结果"));
        //封装头部的列名到list中
        bulidDynamicTitle(1,subjectList,titleList);
        //设置列名的名称和样式
        setCellStyle(  titleList,  row,  alignStyle,  wb, 8);

        //写入每行的正式数据
        for (int i = 0; i < exportEnterpriseExamineeScoreVoList.size(); i++) {
            //创建行
            row = sheet.createRow(i + 1);
            //在行里插入内容
            row.createCell(0).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getAdmissionNo());
            row.createCell(1).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getName());
            row.createCell(2).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getSex());
            row.createCell(3).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getIdcard());
            row.createCell(4).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getMobile());
            row.createCell(5).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getEducation());
            row.createCell(6).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getTotalScore().doubleValue());
            row.createCell(7).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getQualifiedStatus());


            //动态的列
            List<ExportEnterpriseExamineeSubjectGradeScoreVo> exportEnterpriseExamineeSubjectGradeScoreVos = exportEnterpriseExamineeScoreVoList.get(i).getExportEnterpriseExamineeSubjectGradeScoreVos();
            bulidDynamicCellValue(8,  row,  subjectList, exportEnterpriseExamineeSubjectGradeScoreVos);
        }
        /**
         * 设置单元格宽度
         * 上面的操作已经是生成一个完整的文件了，只需要将生成的流转换成文件即可；
         * 下面的设置宽度可有可无，对整体影响不大
         */
        for (int i = 0; i <= titleList.size(); i++) {
            sheet.setColumnWidth(i, 3300);
        }
        return wb;
    }


    private static  void bulidDynamicCellValue(int insertNum,XSSFRow row,List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList,
                                               List<ExportEnterpriseExamineeSubjectGradeScoreVo> exportEnterpriseExamineeSubjectGradeScoreVos){
        //保证列顺序赋值
        if(CollectionUtils.isEmpty(exportEnterpriseExamineeSubjectGradeScoreVos)){
            for (ExportEnterpriseExamineeSubjectGradeScoreVo subjectGradeScoreVo : subjectList) {
                row.createCell(insertNum).setCellValue("");
                insertNum++;

                for (int i=0;i< subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos().size();i++ ) {
                    row.createCell( insertNum).setCellValue("");
                    insertNum++;
                }
            }
        }
        Map<String,ExportEnterpriseExamineeSubjectGradeScoreVo> scoreMap = exportEnterpriseExamineeSubjectGradeScoreVos.stream().collect
                (Collectors.toMap(ExportEnterpriseExamineeSubjectGradeScoreVo::getSubjectId,e->e,(key1, key2)->key2));
        for (ExportEnterpriseExamineeSubjectGradeScoreVo subjectGradeScoreVo : subjectList) {

            ExportEnterpriseExamineeSubjectGradeScoreVo  examineeSubjectGradeScoreVo = scoreMap.get(subjectGradeScoreVo.getSubjectId());
            if( null == examineeSubjectGradeScoreVo){
                row.createCell(insertNum).setCellValue("");
                insertNum++;
                if(CollectionUtils.isNotEmpty(subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos())){
                    for (int i=0;i< subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos().size();i++ ) {
                        row.createCell( insertNum).setCellValue("");
                        insertNum++;
                    }
                }

                continue;
            }
            if (examineeSubjectGradeScoreVo.getSubjectScore() == null) {
                row.createCell(insertNum).setCellValue("");
            } else {
                row.createCell(insertNum).setCellValue(examineeSubjectGradeScoreVo.getSubjectScore().doubleValue());
            }
            insertNum++;
            List<ExportEnterpriseExamineeStemScoreVo> exportEnterpriseExamineeStemScoreVos = examineeSubjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos();
            if(CollectionUtils.isEmpty(exportEnterpriseExamineeStemScoreVos)){
                if(CollectionUtils.isNotEmpty(subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos())){
                    for (int i=0;i< subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos().size();i++) {
                        row.createCell( insertNum).setCellValue("");
                        insertNum++;
                    }
                }
                continue;
            }
            Map<String,ExportEnterpriseExamineeStemScoreVo> stemScoreMap = exportEnterpriseExamineeStemScoreVos.stream().collect
                    (Collectors.toMap(ExportEnterpriseExamineeStemScoreVo::getStemId,e->e,(key1, key2)->key2));
            for (ExportEnterpriseExamineeStemScoreVo stemScoreVo : subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos()) {
                ExportEnterpriseExamineeStemScoreVo examineeStemScoreVo =  stemScoreMap.get(stemScoreVo.getStemId());
                if(null == examineeStemScoreVo){
                    row.createCell( insertNum).setCellValue("");
                    insertNum++;
                    continue;
                }
                if (examineeStemScoreVo.getStemScore() == null) {
                    row.createCell( insertNum).setCellValue("");
                } else {
                    row.createCell(insertNum).setCellValue(examineeStemScoreVo.getStemScore().doubleValue());
                }
                insertNum++;
            }

        }
    }

    private static void bulidDynamicTitle(int tempgroup,List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList,List<ExcelHead> titleList){
        for (ExportEnterpriseExamineeSubjectGradeScoreVo subjectGradeScoreVo : subjectList) {
            tempgroup++;
            titleList.add(new ExcelHead(tempgroup, subjectGradeScoreVo.getSubjectName()));
            if(CollectionUtils.isNotEmpty(subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos())){
                for (ExportEnterpriseExamineeStemScoreVo stemScoreVo : subjectGradeScoreVo.getExportEnterpriseExamineeStemScoreVos()) {
                    titleList.add(new ExcelHead(tempgroup, stemScoreVo.getStemName()));
                }
            }

        }
    }


    private  static  void setCellStyle(List<ExcelHead> titleList,XSSFRow row,XSSFCellStyle alignStyle,XSSFWorkbook wb,int size){
        int currGroup = 1;
        XSSFCellStyle newStyle = null;
        for (int i = 0; i < titleList.size(); i++) {
            XSSFCell curcell = row.createCell(i);
            curcell.setCellValue(titleList.get(i).getHeadName());
            if (i < size) {
                curcell.setCellStyle(alignStyle);
            } else if (titleList.get(i).getGroup() != currGroup) {
                newStyle = getXSSFCellStyle(wb, titleList.get(i).getGroup());
                curcell.setCellStyle(newStyle);
                currGroup = titleList.get(i).getGroup();
            } else {
                curcell.setCellStyle(newStyle);
            }
        }
    }

    /**
     * 1.1：导出 XSSFWorkbook
     * 导出workbook学生 考试总成绩
     *
     * @param exportEnterpriseExamineeScoreVoList 数据
     * @param subjectList                         excle的列表头
     * @param sheetName                           sheet名称
     * @return
     */
    public static XSSFWorkbook getExamineeExamXSSFWorkbook(List<ExportEnterpriseExamineeScoreVo> exportEnterpriseExamineeScoreVoList,
                                                              List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList, String sheetName) {

        //1.创建XSSFWorkbook，一个XSSFWorkbook对应的Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();

        //2.在work中添加一个sheet,对应excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        XSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);

        //单元格样式设置
        XSSFCellStyle alignStyle = wb.createCellStyle();
        alignStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        alignStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        alignStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        alignStyle.setBorderBottom(BorderStyle.THIN);
        alignStyle.setBorderLeft(BorderStyle.THIN);
        alignStyle.setBorderRight(BorderStyle.THIN);
        alignStyle.setBorderTop(BorderStyle.THIN);
        alignStyle.setFont(titleFont);
        alignStyle.setWrapText(true);

        //3.设置表头，即每个列的列名
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 700);
        //将列名写入
        List<ExcelHead> titleList = new LinkedList<>();
        titleList.add(new ExcelHead(1, "准考证号"));
        titleList.add(new ExcelHead(1, "姓名"));
        titleList.add(new ExcelHead(1, "性别"));
        titleList.add(new ExcelHead(1, "证件号"));
        titleList.add(new ExcelHead(1, "手机号码"));
        titleList.add(new ExcelHead(1, "学历"));
        titleList.add(new ExcelHead(1, "总分"));
        titleList.add(new ExcelHead(1, "考试结果"));
        //封装头部的列名到list中
        bulidDynamicTitle(1,subjectList,titleList);
        //设置列名的名称和样式
        setCellStyle(  titleList,  row,  alignStyle,  wb, 8);

        //写入每行的正式数据
        for (int i = 0; i < exportEnterpriseExamineeScoreVoList.size(); i++) {
            //创建行
            row = sheet.createRow(i + 1);
            //在行里插入内容
            row.createCell(0).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getAdmissionNo());
            row.createCell(1).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getName());
            row.createCell(2).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getSex());
            row.createCell(3).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getIdcard());
            row.createCell(4).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getMobile());
            row.createCell(5).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getEducation());
            row.createCell(6).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getTotalScore().doubleValue());
            row.createCell(7).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getQualifiedStatus());


            //动态的列
            bulidDynamicCellValue(8,  row,  subjectList, exportEnterpriseExamineeScoreVoList.get(i).getExportEnterpriseExamineeSubjectGradeScoreVos());

        }
        /**
         * 设置单元格宽度
         * 上面的操作已经是生成一个完整的文件了，只需要将生成的流转换成文件即可；
         * 下面的设置宽度可有可无，对整体影响不大
         */
        for (int i = 0; i <= titleList.size(); i++) {
            sheet.setColumnWidth(i, 3300);
        }
        return wb;
    }

    /**
     * 1.1.2：导出 XSSFWorkbook
     * 导出workbook学生 单科考试成绩
     *
     * @param exportEnterpriseExamineeScoreVoList 数据
     * @param subjectList                         excle的列表头
     * @param sheetName                           sheet名称
     * @return
     */
    public static XSSFWorkbook getExamineeSinglSubjectXSSFWorkbook(List<ExportEnterpriseExamineeScoreVo> exportEnterpriseExamineeScoreVoList,
                                                                   List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList, String sheetName) {
        //1.创建XSSFWorkbook，一个XSSFWorkbook对应的Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();

        //2.在work中添加一个sheet,对应excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        XSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);

        //单元格样式设置
        XSSFCellStyle alignStyle = wb.createCellStyle();
        alignStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        alignStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        alignStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        alignStyle.setBorderBottom(BorderStyle.THIN);
        alignStyle.setBorderLeft(BorderStyle.THIN);
        alignStyle.setBorderRight(BorderStyle.THIN);
        alignStyle.setBorderTop(BorderStyle.THIN);
        alignStyle.setFont(titleFont);
        alignStyle.setWrapText(true);


        //3.设置表头，即每个列的列名
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 700);
        //将列名写入
        List<ExcelHead> titleList = new LinkedList<>();
        titleList.add(new ExcelHead(1, "准考证号"));
        titleList.add(new ExcelHead(1, "姓名"));
        titleList.add(new ExcelHead(1, "性别"));
        titleList.add(new ExcelHead(1, "证件号"));
        titleList.add(new ExcelHead(1, "手机号码"));
        titleList.add(new ExcelHead(1, "学历"));
        titleList.add(new ExcelHead(1, "考试结果"));
        //封装头部的列名到list中
        bulidDynamicTitle(1,subjectList,titleList);
        //设置列名的名称和样式
        setCellStyle(  titleList,  row,  alignStyle,  wb, 7);

        //写入每行的正式数据
        for (int i = 0; i < exportEnterpriseExamineeScoreVoList.size(); i++) {
            //创建行
            row = sheet.createRow(i + 1);
            //在行里插入内容
            row.createCell(0).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getAdmissionNo());
            row.createCell(1).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getName());
            row.createCell(2).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getSex());
            row.createCell(3).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getIdcard());
            row.createCell(4).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getMobile());
            row.createCell(5).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getEducation());
            row.createCell(6).setCellValue(exportEnterpriseExamineeScoreVoList.get(i).getQualifiedStatus());

            //动态的列
            List<ExportEnterpriseExamineeSubjectGradeScoreVo> exportEnterpriseExamineeSubjectGradeScoreVos = exportEnterpriseExamineeScoreVoList.get(i).getExportEnterpriseExamineeSubjectGradeScoreVos();
            bulidDynamicCellValue(7,  row,  subjectList, exportEnterpriseExamineeSubjectGradeScoreVos);


        }
        /**
         * 设置单元格宽度
         * 上面的操作已经是生成一个完整的文件了，只需要将生成的流转换成文件即可；
         * 下面的设置宽度可有可无，对整体影响不大
         */
        for (int i = 0; i <= titleList.size(); i++) {
            sheet.setColumnWidth(i, 3300);
        }
        return wb;
    }

    
    
    
    /**
     * 1.2：根据文本和文件全路径导出excel
     *
     * @param xssfWorkbook 文本内容
     * @param fileEndName  sheet名称
     */
    public static AjaxResult exportExcel(XSSFWorkbook xssfWorkbook, String fileEndName) {
        FileOutputStream out = null;
        String filename = null;
        try {
            filename = ExcelUtil.encodingFilename(fileEndName);
            out = new FileOutputStream(ExcelUtil.getAbsoluteFile(filename));

            xssfWorkbook.write(out);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new CustomException("导出Excel失败，请联系管理员！");
        } finally {
            if (xssfWorkbook != null) {
                try {
                    xssfWorkbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return AjaxResult.success(filename);
    }
    
    
    /**
     * 1.2：根据文本和文件全路径导出excel
     *
     * @param xssfWorkbook 文本内容
     * @param fileEndName  sheet名称
     */
    public String exportExcelV2(XSSFWorkbook xssfWorkbook, String fileEndName) {
    	ByteArrayOutputStream bos = new ByteArrayOutputStream();
        String filename = null;
        try {
            filename = ExcelUtil.encodingFilename(fileEndName);
            xssfWorkbook.write(bos);
            // 上传到华为云
            return fileUpHwCloudService.uploadFileToHuawei(bos.toByteArray(), "xlsx", filename);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new CustomException("导出Excel失败，请联系管理员！");
        } finally {
            if (xssfWorkbook != null) {
                try {
                    xssfWorkbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                	bos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取导出模板的输出流
     *
     * @param subjectList 科目信息
     * @param sheetName   sheet名
     * @return
     */
    public static XSSFWorkbook getExportTep(List<ExportEnterpriseExamineeSubjectGradeScoreVo> subjectList, String sheetName,
                                            String[] sexKeys, String[] educationKeys) {

        //1.创建XSSFWorkbook，一个XSSFWorkbook对应的Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();

        //2.在work中添加一个sheet,对应excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);

        //单元格样式设置
        XSSFCellStyle alignStyle = wb.createCellStyle();
        alignStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        alignStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        alignStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
        alignStyle.setBorderBottom(BorderStyle.THIN);
        alignStyle.setBorderLeft(BorderStyle.THIN);
        alignStyle.setBorderRight(BorderStyle.THIN);
        alignStyle.setBorderTop(BorderStyle.THIN);
        alignStyle.setWrapText(true);


        //3.设置表头，即每个列的列名
        //创建第一行
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 700);
        //将列名写入
        List<ExcelHead> titleList = new LinkedList<>();
        titleList.add(new ExcelHead(1, "准考证号"));
        titleList.add(new ExcelHead(1, "姓名"));
        titleList.add(new ExcelHead(1, "性别"));
        titleList.add(new ExcelHead(1, "证件号"));
        titleList.add(new ExcelHead(1, "手机号码"));
        titleList.add(new ExcelHead(1, "学历"));

        //封装头部的列名到list中
        int tempgroup = 1;
        for (int i = 0; i < subjectList.size(); i++) {
            tempgroup++;
            titleList.add(new ExcelHead(tempgroup, subjectList.get(i).getSubjectName()));
            for (int j = 0; j < subjectList.get(i).getExportEnterpriseExamineeStemScoreVos().size(); j++) {
                titleList.add(new ExcelHead(tempgroup, subjectList.get(i).getExportEnterpriseExamineeStemScoreVos().get(j).getStemName()));
            }
        }
        //设置列名的名称和样式
        int currGroup = 1;
        XSSFCellStyle newStyle = null;
        for (int i = 0; i < titleList.size(); i++) {
            XSSFCell curcell = row.createCell(i);
            curcell.setCellValue(titleList.get(i).getHeadName());
            if (i < 6) {
                curcell.setCellStyle(alignStyle);
            } else if (titleList.get(i).getGroup() != currGroup) {
                newStyle = getXSSFCellStyle(wb, titleList.get(i).getGroup());
                curcell.setCellStyle(newStyle);
                currGroup = titleList.get(i).getGroup();
            } else {
                curcell.setCellStyle(newStyle);
            }
        }
        //INFO 这边暂时设置一万行为最多
        setDropDownBox(sheet, sexKeys, 1, 10000, 2, 2);
        setDropDownBox(sheet, educationKeys, 1, 10000, 5, 5);

        /**
         * 设置单元格宽度
         * 上面的操作已经是生成一个完整的文件了，只需要将生成的流转换成文件即可；
         * 下面的设置宽度可有可无，对整体影响不大
         */
        for (int i = 0; i <= titleList.size(); i++) {
            sheet.setColumnWidth(i, 3300);
            //INFO 把所有列得类型设置为string字符串类型，如有需要可以对不同列进行更改
            XSSFCellStyle textStyle = wb.createCellStyle();
            XSSFDataFormat format = wb.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            sheet.setDefaultColumnStyle(i, textStyle);
        }
        return wb;
    }

    /**
     * 设置下拉框内容
     *
     * @param sheet    指定sheet页
     * @param values   下拉框的值
     * @param firstRow 起始行号
     * @param lastRow  起始行号
     * @param firstCol 起始列号
     * @param lastCol  终止列号
     */
    public static void setDropDownBox(XSSFSheet sheet, String[] values, Integer firstRow, Integer lastRow, Integer
            firstCol, Integer lastCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(values);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
        //这两行设置单元格只能是列表中的内容，否则报错
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    /**
     * 获取表格样式
     *
     * @param wb
     * @return
     */
    public static XSSFCellStyle getXSSFCellStyle(XSSFWorkbook wb, int index) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setWrapText(true);
        style.setFillForegroundColor(getRandomColorIndex(index));
        return style;
    }

    /**
     * 获取随机颜色的值
     *
     * @return
     */
    private static short getRandomColorIndex(int group) {
        //之所以这样，是因为这些颜色做为背景色较浅
        short[] colorIndex = {
                IndexedColors.AQUA.getIndex(),
                IndexedColors.BLUE.getIndex(),
                IndexedColors.BLUE_GREY.getIndex(),
                IndexedColors.BRIGHT_GREEN.getIndex(),
                IndexedColors.CORAL.getIndex(),
                IndexedColors.CORNFLOWER_BLUE.getIndex(),
                IndexedColors.GOLD.getIndex(),
                IndexedColors.GREY_25_PERCENT.getIndex(),
                IndexedColors.GREY_40_PERCENT.getIndex(),
                IndexedColors.GREY_50_PERCENT.getIndex(),
                IndexedColors.LAVENDER.getIndex(),
                IndexedColors.LEMON_CHIFFON.getIndex(),
                IndexedColors.LIGHT_BLUE.getIndex(),
                IndexedColors.LEMON_CHIFFON.getIndex(),
                IndexedColors.LIGHT_BLUE.getIndex(),
                IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex(),
                IndexedColors.LIGHT_GREEN.getIndex(),
                IndexedColors.LIGHT_ORANGE.getIndex(),
                IndexedColors.LIGHT_TURQUOISE.getIndex(),
                IndexedColors.LIGHT_YELLOW.getIndex(),
                IndexedColors.OLIVE_GREEN.getIndex(),
                IndexedColors.PALE_BLUE.getIndex(),
                IndexedColors.PINK.getIndex(),
                IndexedColors.RED.getIndex(),
                IndexedColors.ROSE.getIndex(),
                IndexedColors.ROYAL_BLUE.getIndex(),
                IndexedColors.SEA_GREEN.getIndex(),
                IndexedColors.SKY_BLUE.getIndex(),
                IndexedColors.TAN.getIndex(),
                IndexedColors.TEAL.getIndex(),
                IndexedColors.TURQUOISE.getIndex(),
                IndexedColors.YELLOW.getIndex()
        };
        int index = group % colorIndex.length;
        return colorIndex[index];
    }
}
